﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Threading;

namespace ExcelAutomationRunner
{

    public abstract class ExcelApplicationBase
    {

        private readonly static object _lock = new object();

        [DllImport( "user32.dll", SetLastError = true )]
        public static extern uint GetWindowThreadProcessId( IntPtr hwnd, out uint lpdwProcessId );

        protected Workbook _newWorkbook;
        protected string _newPath = string.Empty;
        protected readonly int _maxMillisecondsToReleaseExcel;

        public ExcelApplicationBase( int? maxMillisecondsToReleaseExcel = null )
        {
            _maxMillisecondsToReleaseExcel = maxMillisecondsToReleaseExcel ?? 1500;
        }

        /// <summary>
        /// This is the function that will do whatever you need to do with the app object.
        /// </summary>
        /// <param name="excel"></param>
        /// <returns></returns>
        protected abstract Tuple<bool, string> Process( Application excel );

        protected static Range ColumnWithHead( string s, Worksheet existingWorksheet )
        {
            for ( int i = 1; i <= existingWorksheet.UsedRange.Columns.Count; i++ )
            {
                Range headerCell = existingWorksheet.Cells[1, i];
                if ( headerCell.Text == s )
                {
                    return existingWorksheet.UsedRange.Columns[i];
                }
            }
            return null;
        }

        protected static int ColumnIndexWithHead( string s, Worksheet existingWorksheet )
        {
            for ( int i = 1; i <= existingWorksheet.UsedRange.Columns.Count; i++ )
            {
                Range headerCell = existingWorksheet.Cells[1, i];
                if ( headerCell.Text == s )
                {
                    return i;
                }
            }
            return 0;
        }

        protected Tuple<bool, string> ProcessApplication()
        {
            var result = new Tuple<bool, string>( false, "Unknown error working with Excel Automation." );
            Application excel = null;

            try
            {
                excel = new Application { Visible = false, Interactive = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
                result = Process( excel );
            }
            catch ( Exception e )
            {
                result = new Tuple<bool, string>( false, string.Format( "Error working with Excel Automation: {0}", e.Message ) );
            }
            finally
            {
                if ( excel != null )
                {
                    try
                    {
                        Close( excel );
                        if ( File.Exists( _newPath ) )  // sometimes new file not created.
                        {
                            if ( !TryAccess( _newPath, _maxMillisecondsToReleaseExcel ) )
                            {
                                /// We need to be SURE to be able to access the file at this point.  Downstream processes will need the file.
                                result = new Tuple<bool, string>( false, string.Format( "Unable to release excel and access file.{0}New file path:{1}", Environment.NewLine, _newPath ) );
                            }
                        }
                    }
                    catch ( Exception ex )
                    {
                        /// I have no idea why calling Quit should blow up, but I've seen it happen.  Internets don't know either.
                        result = new Tuple<bool, string>( false, string.Format( "Unable to properly quit excel: {0}{1}New file path:{2}", ex.Message, Environment.NewLine, _newPath ) );
                    }
                }
            }
            return result;

        }

        /// <summary>
        /// The excel COM object doesn't like to quit when running under IIS it seems.
        /// If the process doesn't quit properly, another copy of Excel opened (on dev machine) won't respond properly.  Of course, we don't want copies of Excel to build up either.
        /// </summary>
        /// <param name="excel"></param>
        private void Close( Application excel )
        {
            ReleaseComObject( _newWorkbook );
            var ticks = Environment.TickCount;

            while ( ( !excel.Ready ) && ( ( Environment.TickCount - ticks ) < _maxMillisecondsToReleaseExcel ) )
            {
                Thread.Sleep( 50 );
            }

            if ( ( Environment.TickCount - ticks ) > _maxMillisecondsToReleaseExcel )
            {
                throw new Exception( string.Format( "Excel application won't quit (continues to return false to Ready).{0}New file path: {1}", Environment.NewLine, _newPath ) );
            }
            else
            {
                /// ########  #### ########    #### #### #### #### 
                /// ##     ##  ##  ##          #### #### #### #### 
                /// ##     ##  ##  ##          #### #### #### #### 
                /// ##     ##  ##  ######       ##   ##   ##   ##  
                /// ##     ##  ##  ##                              
                /// ##     ##  ##  ##          #### #### #### #### 
                /// ########  #### ########    #### #### #### #### 
                var processId = ProcessId( excel );
                excel.Quit();
                KillExcel( processId );
                ReleaseComObject( excel );
                excel = null;  // for GC
            }
        }

        /// <summary>
        /// In dev, I don't have to call this.  No Excel process hangs around in the task list.  On the server behind IIS, it does.
        /// http://stackoverflow.com/questions/18129527/process-kill-access-denied  -- Was getting this when Excel 64-Bit was installed on server with IIS running with setting "enable 32-Bit Applications" true.
        /// </summary>
        /// <param name="processId"></param>
        protected void KillExcel( uint processId )
        {
            try
            {
                // WindowsIdentity.GetCurrent().Name - gives the user under which IIS is running.
                var excelProcess = System.Diagnostics.Process.GetProcessById( (int)( processId ) );
                if ( ( excelProcess != null ) && !excelProcess.HasExited )
                {
#if(DEBUG)
                    /// The reason this gets weird in dev (pretty sure) is because some of us have 32 bit and some 64.
                    excelProcess.Kill();
                    excelProcess.WaitForExit( _maxMillisecondsToReleaseExcel );
                    // https://msdn.microsoft.com/en-us/library/system.diagnostics.process.kill%28v=vs.110%29.aspx
#else
                    /// If I call this, *one* copy of Excel hangs around.  This would seem to be what we want; additional copies take no time to start up.
                    /// If I call kill, no copy is left behind.
                    /// But not working in dev.
                    excelProcess.CloseMainWindow();
#endif
                }
            }
            catch ( Exception )
            {
                ;
            }
        }

        /// <summary>
        /// https://support.microsoft.com/kb/317109?wa=wsignin1.0
        /// Not sure how many times or when I have to call this, but at this point, excel no longer remains in memory (task list).
        /// I'm also not sure whether there is any way to *leave* one copy in memory so subsequent calls can be faster.
        /// Note: above comments seem only to apply when running behind IIS (much trickier).
        /// http://stackoverflow.com/questions/9003597/how-to-force-excel-process-to-quit-after-com-automation  -- Sticking around for reason?
        /// http://stackoverflow.com/questions/17781708/why-is-killing-the-excel-process-a-bad-thing/17798303#17798303
        /// </summary>
        /// <param name="o"></param>
        protected static void ReleaseComObject( object o )
        {
            if ( o != null )
            {
                var result = Marshal.ReleaseComObject( o );
                while ( result > 0 )
                {
                    result = Marshal.ReleaseComObject( o );
                }
                o = null;
            }
        }

        public static uint ProcessId( Application a )
        {
            uint result;
            GetWindowThreadProcessId( new IntPtr( a.Hwnd ), out result );
            return result;
        }

        /// <summary>
        /// Get an entire column.
        /// </summary>
        protected static Range GetUsedColumnRange( Worksheet existingWorksheet, int column )
        {
            Range firstCell = existingWorksheet.Cells[1, column];
            Range lastCell = existingWorksheet.Cells[existingWorksheet.UsedRange.Rows.Count, column];  // UsedRange: non-empty cells
            Range result = existingWorksheet.Range[firstCell, lastCell];
            ReleaseComObject( firstCell );
            ReleaseComObject( lastCell );
            return result;

            /// Was copying incorrectly...
            //existingWorksheet.Range["A1"].Select();
            //var firstCell = ( (Range)excel.Selection ).End[XlDirection.xlUp];
            //var lastCell = ( (Range)excel.Selection ).End[XlDirection.xlDown];
            //existingWorksheet.Range[firstCell, lastCell].Select();
        }

        /// <summary>
        /// Don't know of any IsLocked function from the OS.
        /// </summary>
        /// <param name="path"></param>
        /// <param name="attemptWaitMS"></param>
        /// <returns></returns>
        /// <remarks>Thread safe</remarks>
        public static bool TryAccess( string path, int attemptWaitMS )
        {
            lock ( _lock )
            {
                var beginTime = Environment.TickCount;
                while ( true )
                {
                    try
                    {
                        File.SetLastWriteTime( path, DateTime.Now );  // won't work if file is locked by another process.  Basically File.Touch
                        return true;
                    }
                    catch ( Exception )
                    {
                        if ( ( Environment.TickCount - beginTime > attemptWaitMS ) )
                        {
                            return false;
                        }
                        else
                        {
                            Thread.Sleep( 10 );
                        }
                    }
                }
            }
        }

    }
}
